In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name. The term pivot table is a generic phrase used by multiple vendors. However, Microsoft Corporation has trademarked the specific form PivotTable.[1][2]
Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.
Contents |
In their book Pivot Table Data Crunching,[3] authors Bill Jelen and Mike Alexander refer to Pito Salas as the "father of pivot tables". While working on a concept for a new program which would eventually become Lotus Improv, Salas realized that spreadsheets have patterns of data. A tool that could help the user recognize these patterns would help to build advanced data models quickly. With Improv, users could define and store sets of categories, then change views by dragging category names with the mouse. This core functionality would provide the model for pivot tables.
Lotus Development released Improv in 1991 on the NeXT platform. A few months after the release of Improv, Brio Technology published a standalone Mac implementation called DataPivot (with technology eventually patented in 1999[4]). Borland purchased the DataPivot technology in 1992 and implemented it in their own spreadsheet application Quattro Pro.
In 1993, at the time when the Windows version of Improv appeared, Microsoft Excel 5 was already on the market with a new functionality called a “PivotTable”. This functionality was further improved in later Excel versions:
For typical data entry and storage, data usually appear in flat tables, meaning that it consists of only columns and rows, as in the following example showing data on shirt types:
While those data can contain a lot of information, it can be difficult to get summarized information. A pivot table can help quickly summarize the data and highlight the desired information. The usage of a pivot table is extremely broad and depends on the situation. The first question to ask is, "What am I looking for?" In the example here, let us ask, "How many Units did we sell in each Region for every Ship Date?":
A pivot table usually consists of row, column, and data (or fact) fields. In this case, the column is Ship Date, the row is Region, and the data we would like to see is Units. These fields allow several kinds of aggregations including: sum, average, standard deviation, count, etc. In this case, the total number of units shipped is displayed here using a sum aggregation.
Using the example above, software will find all distinct records for Region. In this case, they are: North, South, East, West. Furthermore, it will find all distinct records for Ship Date. Based on the aggregation type, sum, it will summarize the fact, and display them in a multidimensional chart. In the example above, the first data point is 66. This number was obtained by finding all records where both Region was East and Ship Date was 1/31/2005, and adding the Units of that collection of records together to get a final result.
Pivot tables are an integral part of a spreadsheet application. In addition to Microsoft Excel, competing software programs such as OpenOffice.org Calc provide similar functionality; the implementation in OpenOffice.org and in the related LibreOffice up to release 3.3 is called DataPilot. In LibreOffice 3.4 DataPilot is renamed 'Pivot Table'. Other companies such as numberGo and Quantrix provide similar implementations.
Pivot functionality also operates in other data visualization tools, including business intelligence packages.
Google Docs initially allowed the creation of basic pivot tables via the pivot table gadget from Panorama called Panorama Analytics, but as of 2011[update] this gadget provided limited functionality and was extremely slow with large amounts of data. In May 2011, Google announced the roll-out of a natively hosted pivot table feature in the Google spreadsheets editor. [5]
ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications (Demo).
Excel Pivot Tables includes the feature to directly query an OLAP server for retrieving data instead of getting the data from an Excel spreadsheet. On this configuration a pivot table is a simple client of an OLAP server. Excel's Pivot Table not only allows for connecting to Microsoft's solution (Analysis Service), but to any XMLA (OLAP standard) compliant server.
Other OLAP clients are JPivot, Dundas, IcCube (Client Library).